/****** Object:  StoredProcedure [dbo].[AuthZ_spGetRolesByUser]    Script Date: 06/24/2009 23:46:36 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthZ_spGetRolesByUser]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AuthZ_spGetRolesByUser]
GO

/****** Object:  StoredProcedure [dbo].[AuthZ_spGetRolesByUser]    Script Date: 06/24/2009 23:46:36 ******/
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:		<revans>
-- Create date: <5-17-2009>
-- Description:	<Retrieves all roles for user (including inherited roles).>
-- Param:
-- =============================================
CREATE PROCEDURE [dbo].[AuthZ_spGetRolesByUser]

	@personnelNumber as int

AS
BEGIN
	BEGIN TRY
		SET NOCOUNT ON;
		
		SELECT 
		r.RoleName
		FROM [dbo].[AuthZUserRole] aur
		JOIN [dbo].[Role] r
		ON   r.RoleID = aur.RoleID
		JOIN [dbo].[AuthZUser] au
		ON au.AuthZUserID = aur.AuthZUserID
		AND au.PersonnelNumber = @personnelNumber

	END TRY
BEGIN CATCH
	DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
           
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH
END

GO


